﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;

namespace PatientInformationExtract.MainInterface.Models.XMLCS
{
    class ExcelOperate
    {
        private int _rowsint;
        public int RowsCount { get { return this._rowsint; } }
        private int _columnsint;
        public int ColumnsCount { get { return this._columnsint; } }
        private string _sheetName;
        public string SheetName { get { return this._sheetName; } }
        private string _filePath;
        public string FilePath { get { return this._filePath; } }
        private int _hdr;
        public int Hdr { get { return this._hdr; } }
        private Workbook _excelWB;//创建工作簿（WorkBook：即Excel文件主体本身）  
        private Worksheet _excelWS;//创建工作表（即Excel里的子表sheet）
        private Sheets _excelSts;

        public ExcelOperate(string filePath, int hdr, ref Workbooks excelWBs)
        {
            _hdr = hdr;
            _filePath = filePath;

            //打开一个现有的工作薄  
            _excelWB = excelWBs.Open(filePath);
            _excelSts = _excelWB.Sheets;
            //选择第一个Sheet页  
            _excelWS = _excelSts.get_Item(1);

            _sheetName = _excelWS.Name;
            _rowsint = _excelWS.UsedRange.Cells.Rows.Count; //得到行数
            _columnsint = _excelWS.UsedRange.Cells.Columns.Count;//得到列数
        }

        public void Release()
        {
            _excelWB.Close();
            ReleaseCOM(_excelWS);
            ReleaseCOM(_excelSts);
            ReleaseCOM(_excelWB);
        }

        public System.Data.DataTable ToDataTable()
        {
            System.Data.DataTable dt = new System.Data.DataTable(_sheetName);
            if (_hdr == 1)
            {
                for (int i = 1; i <= _columnsint; i++)
                {
                    dt.Columns.Add("Cloumn" + i.ToString(), System.Type.GetType("System.String"));
                }
            }
            else
            {
                for (int i = 1; i <= _columnsint; i++)
                {
                    dt.Columns.Add(((Range)_excelWS.Cells[1, i]).Text.ToString(), System.Type.GetType("System.String"));
                }
            }

            for (int i = _hdr; i <= _rowsint; i++)//行
            {
                DataRow dr = dt.NewRow();
                for (int j = 1; j <= _columnsint; j++)//列
                {
                    dr[j - 1] = ((Range)_excelWS.Cells[i, j]).Text.ToString();
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        public string GetColumnStringByIndex(int columnIndex)
        {
            string str;
            str = ((Range)_excelWS.Cells[_hdr, columnIndex]).Text.ToString();
            for (int i = _hdr; i <= _rowsint; i++)
            {
                if (!((Range)_excelWS.Cells[i, columnIndex]).Text.ToString().Equals(str))
                {
                    throw new Exception(String.Format(PatientInfoErrorMessages.ColoumnValuesDisaccord, columnIndex, _sheetName, _filePath));
                }
            }
            return str;
        }

        public string GetCellStringByIndex(int rowIndex, int columnIndex)
        {
            return ((Range)_excelWS.Cells[rowIndex, columnIndex]).Text.ToString();
        }



        public string[] GetColumnByIndex(int columnIndex)
        {
            string[] str = new string[_rowsint - _hdr + 1];
            for (int i = _hdr; i <= _rowsint; i++)
            {
                str[i - _hdr] = ((Range)_excelWS.Cells[i, columnIndex]).Text;
            }
            return str;
        }

        public string[] GetColumnByIndex2(int columnIndex)
        {
            string[] str = new string[_rowsint - _hdr + 1];
            for (int i = _hdr; i <= _rowsint; i++)
            {
                str[i - _hdr] = (((Range)_excelWS.Cells[i, columnIndex]).Value as double?).ToString();
            }
            return str;
        }



        // 释放资源  
        private void ReleaseCOM(object pObj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
            }
            catch (Exception ex)
            {
                throw new Exception(String.Format(PatientInfoErrorMessages.ReleasingCOMFailed, ex.Message), ex);
            }
            finally
            {
                pObj = null;
            }
        }
    }
}
